package com.pic.mgt.dao.haishin;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Vector;

import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;

import com.pic.mgt.dao.BusinessTargetActionDao;
import com.pic.mgt.jdbc.CachedPreparedStatemntJdbcOperations;
import com.pic.mgt.jdbc.CachedPreparedStatemntJdbcTemplate;
import com.pic.mgt.utils.IgxCollectionUtils;
import com.pic.mgt.utils.SqlScriptUtils;


public final class HaishinBusinessTargetActionDao extends BusinessTargetActionDao implements
											IHaishinBusinessTargetActionDao {
	private CachedPreparedStatemntJdbcOperations cachedPreparedStatemntJdbcOperations;
	
	private final String tableName = "haishin_business_target_action";
	
	private final String findHaishinBasicBusinessTargetActionInfoListByUnitNameWithOrderByCreatingTimeDESCSqlScript;
	private final String findHaishinBasicBusinessTargetActionInfoByFilenameSqlScritp;
	
	// Insert Basic 相關設定
	private final Vector<String> insertBasicFieldsVector;
	private final String insertBasicSqlScript;
	private final long insertBasicTimeout = 5 * 1000;
	
	public HaishinBusinessTargetActionDao() {
		super();
		
		insertBasicFieldsVector = new Vector<String>();
		
		// 建立 Insert Basic 的 Sql Script
		this.insertBasicSqlScript = createInsertBasicSQLScript();
		
		// 建立 find haishin basic business target action info list by unit name with order by creating time desc 的 sql script
		this.findHaishinBasicBusinessTargetActionInfoListByUnitNameWithOrderByCreatingTimeDESCSqlScript =
				createFindHaishinBasicBusinessTargetActionInfoListByUnitNameWithOrderByCreatingTimeDESCSqlScript();
		
		// 建立 find haishin basic business target action info by file name 的 Sql Script
		this.findHaishinBasicBusinessTargetActionInfoByFilenameSqlScritp = createFindHaishinBasicBusinessTargetActionInfoByFilenameSqlScritp();
	}
	
	private String createFindHaishinBasicBusinessTargetActionInfoByFilenameSqlScritp() {
		Vector<String> selectFieldsVector =
			new Vector<String>();

		selectFieldsVector.add(_terminalId);
		selectFieldsVector.add(_fileName);
		selectFieldsVector.add(_fileSize);
		selectFieldsVector.add(_lastTxTime);
		selectFieldsVector.add(_creatingTime);
		selectFieldsVector.add(_result);

		return SqlScriptUtils.createNamedParamQueryScriptWithSingleCondition(
				getTableName(), selectFieldsVector, _fileName);
	}
	
	private String createFindHaishinBasicBusinessTargetActionInfoListByUnitNameWithOrderByCreatingTimeDESCSqlScript() {
		Vector<String> selectFieldsVector =
			new Vector<String>();

		selectFieldsVector.add(_terminalId);
		selectFieldsVector.add(_fileName);
		selectFieldsVector.add(_fileSize);
		selectFieldsVector.add(_lastTxTime);
		selectFieldsVector.add(_creatingTime);
		selectFieldsVector.add(_result);

		return SqlScriptUtils.createNamedParamQueryScriptWithSorting(
				getTableName(),
				selectFieldsVector,
				_unitName,
				new String[] {_creatingTime, "DESC"});
	}
	
	private String createInsertBasicSQLScript() {
		// 要新增的欄位
		insertBasicFieldsVector.add(_targetFile);
		insertBasicFieldsVector.add(_unitName);
		insertBasicFieldsVector.add(_terminalId);
		insertBasicFieldsVector.add(_fileName);
		insertBasicFieldsVector.add(_fileSize);
		insertBasicFieldsVector.add(_result);

		return
			 SqlScriptUtils.createInsertScript(this.tableName,
			    					    	   insertBasicFieldsVector);
	}

	@Override
	public int insertBasic(Map<String, Object> insertValuesMap)
			throws DataAccessException {
		if (null != insertValuesMap && !insertValuesMap.isEmpty()) {
			// 將 inserValuesMap 內的值做正確順序排序
			Object[] orderedFieldValues = 
				CachedPreparedStatemntJdbcTemplate.
					sortValuesByGivingFieldVector(insertValuesMap,
												  this.insertBasicFieldsVector);
			// 新增資料
			return
				this.cachedPreparedStatemntJdbcOperations.
								executeUpdate(this.insertBasicSqlScript,
										orderedFieldValues);
		}
		else {
			return 0;
		}
	}
	
	@Override
	public List<HaishinBasicBusinessTargetActionInfoBean> findHaishinBasicBusinessTargetActionInfoListByUnitNameWithOrderByCreatingTimeDESC(
			String unitName) throws DataAccessException {
		
		return
		super.namedParameterJdbcTemplate.query(
				this.findHaishinBasicBusinessTargetActionInfoListByUnitNameWithOrderByCreatingTimeDESCSqlScript,
				IgxCollectionUtils.convertToMap(
						new Object[] {_unitName, unitName}),
			new RowMapper<HaishinBasicBusinessTargetActionInfoBean>() {
				public HaishinBasicBusinessTargetActionInfoBean mapRow(ResultSet rs, int rowNum) throws SQLException {
					HaishinBasicBusinessTargetActionInfoBean haishinBasicBusinessTargetActionInfoBean = new HaishinBasicBusinessTargetActionInfoBean();						
					
					haishinBasicBusinessTargetActionInfoBean.setTerminalId(rs.getString(_terminalId));
					haishinBasicBusinessTargetActionInfoBean.setFilename(rs.getString(_fileName));
					haishinBasicBusinessTargetActionInfoBean.setFilesize(rs.getLong(_fileSize));
					haishinBasicBusinessTargetActionInfoBean.setResult(rs.getString(_result));
					haishinBasicBusinessTargetActionInfoBean.setCreatingTime(new Date(rs.getTimestamp(_creatingTime).getTime()));
					
					Timestamp latestTxTimestamp = rs.getTimestamp(_lastTxTime);
					if (null != latestTxTimestamp) {
						haishinBasicBusinessTargetActionInfoBean.setLastTransactionTime(new Date(latestTxTimestamp.getTime()));
					}
					else {
						haishinBasicBusinessTargetActionInfoBean.setLastTransactionTime(null);
					}
					
					return haishinBasicBusinessTargetActionInfoBean;
				}
			  });
	}
	
	@Override
	public HaishinBasicBusinessTargetActionInfoBean findHaishinBasicBusinessTargetActionInfoByFilename(
			String filename) throws DataAccessException {
		try {
			return
			super.namedParameterJdbcTemplate.queryForObject(
					this.findHaishinBasicBusinessTargetActionInfoByFilenameSqlScritp,
					Collections.singletonMap((String)_fileName, (Object)filename),
					new RowMapper<HaishinBasicBusinessTargetActionInfoBean>() {
						public HaishinBasicBusinessTargetActionInfoBean mapRow(ResultSet rs, int rowNum) throws SQLException {
							HaishinBasicBusinessTargetActionInfoBean haishinBasicBusinessTargetActionInfoBean = new HaishinBasicBusinessTargetActionInfoBean();
						
							haishinBasicBusinessTargetActionInfoBean.setTerminalId(rs.getString(_terminalId));
							haishinBasicBusinessTargetActionInfoBean.setFilename(rs.getString(_fileName));
							haishinBasicBusinessTargetActionInfoBean.setFilesize(rs.getLong(_fileSize));
							haishinBasicBusinessTargetActionInfoBean.setResult(rs.getString(_result));
							haishinBasicBusinessTargetActionInfoBean.setCreatingTime(new Date(rs.getTimestamp(_creatingTime).getTime()));
							
							Timestamp latestTxTimestamp = rs.getTimestamp(_lastTxTime);
							if (null != latestTxTimestamp) {
								haishinBasicBusinessTargetActionInfoBean.setLastTransactionTime(new Date(latestTxTimestamp.getTime()));
							}
							else {
								haishinBasicBusinessTargetActionInfoBean.setLastTransactionTime(null);
							}
							
							return haishinBasicBusinessTargetActionInfoBean;
						}
					}
				);
		} catch (EmptyResultDataAccessException e) {
			return null;
		} catch (DataAccessException e) {
			throw e;
		}
	}
	
	@Override
	public String getTableName() {
		return this.tableName;
	}
	
	// --- getter and setter ---

	public void setCachedPreparedStatemntJdbcOperations(
			CachedPreparedStatemntJdbcOperations cachedPreparedStatemntJdbcOperations) {
		this.cachedPreparedStatemntJdbcOperations = cachedPreparedStatemntJdbcOperations;
		
		this.cachedPreparedStatemntJdbcOperations.
						addPreparedStatementScript(this.insertBasicSqlScript,
						       				       this.insertBasicTimeout);
	}

}
